package top.huhuiyu.study.dao;

import top.huhuiyu.study.entity.TbNotice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbNoticeDAO {
    public static final String INSERT = "insert into tb_notice(username,title,content) values(?,?,?)";
    public static final String QUERY = "select id,username,title,topindex,create_time from tb_notice order by topindex";
    public static final String QUERY_BY_ID = "select * from tb_notice where id = ?";
    public static final String DELETE = "delete from tb_notice where id = ?";
    public static final String UPDATE = "update tb_notice set username=?,title=?,content=?,topindex=? where id = ?";

    public static int insert(TbNotice notice) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, notice.getUsername());
        ps.setString(2, notice.getTitle());
        ps.setString(3, notice.getContent());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static List<TbNotice> query() throws Exception {
        List<TbNotice> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbNotice notice = new TbNotice();
            notice.setId(rs.getInt("id"));
            notice.setUsername(rs.getString("username"));
            notice.setTitle(rs.getString("title"));
            notice.setTopindex(rs.getInt("topindex"));
            notice.setCreate_time(rs.getTimestamp("create_time"));
            list.add(notice);
        }
        return list;
    }
    public static TbNotice queryById(Integer id) throws Exception {
        TbNotice notice = null;
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            notice = new TbNotice();
            notice.setId(rs.getInt("id"));
            notice.setUsername(rs.getString("username"));
            notice.setTitle(rs.getString("title"));
            notice.setTopindex(rs.getInt("topindex"));
            notice.setCreate_time(rs.getTimestamp("create_time"));
            notice.setContent(rs.getString("content"));
        }
        conn.close();
        return notice;
    }

    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static int update(TbNotice notice) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        ps.setString(1, notice.getUsername());
        ps.setString(2, notice.getTitle());
        ps.setString(3, notice.getContent());
        ps.setInt(4, notice.getTopindex());
        ps.setInt(5, notice.getId());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }



}
